from django.db import models
from django.contrib.auth.models import User
from django.utils import timezone
import uuid
import paramiko
import logging
from django.core.exceptions import ValidationError

# 配置日志
logger = logging.getLogger(__name__)

class Server(models.Model):
    """服务器模型，存储远程服务器连接信息"""
    name = models.CharField(max_length=100, verbose_name="服务器名称")
    hostname = models.CharField(max_length=255, verbose_name="主机名/IP地址")
    port = models.IntegerField(default=22, verbose_name="SSH端口")
    username = models.CharField(max_length=100, verbose_name="用户名")
    password = models.CharField(max_length=255, blank=True, null=True, verbose_name="密码")
    private_key = models.TextField(blank=True, null=True, verbose_name="私钥")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="更新时间")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="created_servers", verbose_name="创建人")
    
    class Meta:
        verbose_name = "服务器"
        verbose_name_plural = "服务器"
        ordering = ['name']
    
    def __str__(self):
        return f"{self.name} ({self.hostname}:{self.port})"
    
    def test_connection(self):
        """测试与服务器的连接"""
        try:
            # 创建SSH客户端
            ssh = paramiko.SSHClient()
            # 自动添加主机密钥
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            
            # 尝试连接
            if self.private_key:
                # 使用私钥连接
                private_key = paramiko.RSAKey.from_private_key_file(self.private_key)
                ssh.connect(
                    hostname=self.hostname,
                    port=self.port,
                    username=self.username,
                    pkey=private_key,
                    timeout=10
                )
            else:
                # 使用密码连接
                ssh.connect(
                    hostname=self.hostname,
                    port=self.port,
                    username=self.username,
                    password=self.password,
                    timeout=10
                )
            
            # 连接成功，关闭连接
            ssh.close()
            return True, "连接成功"
            
        except Exception as e:
            logger.error(f"服务器连接测试失败: {str(e)}")
            return False, f"连接失败: {str(e)}"


class Task(models.Model):
    """批处理任务模型"""
    TASK_STATUS_CHOICES = [
        ('draft', '草稿'),
        ('scheduled', '已计划'),
        ('running', '运行中'),
        ('completed', '已完成'),
        ('failed', '已失败'),
    ]
    
    #id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255, verbose_name="任务名称")
    description = models.TextField(blank=True, null=True, verbose_name="任务描述")
    server = models.ForeignKey(Server, on_delete=models.CASCADE, related_name="tasks", verbose_name="目标服务器")
    command = models.TextField(verbose_name="执行命令")
    status = models.CharField(max_length=20, choices=TASK_STATUS_CHOICES, default='draft', verbose_name="任务状态")
    schedule_time = models.DateTimeField(blank=True, null=True, verbose_name="计划执行时间")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="更新时间")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="created_tasks", verbose_name="创建人")
    last_run_time = models.DateTimeField(blank=True, null=True, verbose_name="最后运行时间")
    is_enabled = models.BooleanField(default=True, verbose_name="是否启用")
    
    class Meta:
        verbose_name = "批处理任务"
        verbose_name_plural = "批处理任务"
        ordering = ['-created_at']
    
    def __str__(self):
        return self.name
    
    def run(self, user):
        """执行任务"""
        if self.status == 'running':
            return False, "任务正在运行中"
            
        # 更新任务状态
        self.status = 'running'
        self.last_run_time = timezone.now()
        self.save()
        
        # 创建日志记录
        task_log = TaskLog.objects.create(
            task=self,
            status='running',
            started_by=user
        )
        
        try:
            # 连接服务器并执行命令
            ssh = paramiko.SSHClient()
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            
            # 连接服务器
            if self.server.private_key:
                private_key = paramiko.RSAKey.from_private_key_file(self.server.private_key)
                ssh.connect(
                    hostname=self.server.hostname,
                    port=self.server.port,
                    username=self.server.username,
                    pkey=private_key,
                    timeout=30
                )
            else:
                ssh.connect(
                    hostname=self.server.hostname,
                    port=self.server.port,
                    username=self.server.username,
                    password=self.server.password,
                    timeout=30
                )
            
            # 执行命令
            stdin, stdout, stderr = ssh.exec_command(self.command)
            
            # 读取输出
            output = stdout.read().decode('utf-8')
            error = stderr.read().decode('utf-8')
            
            # 关闭连接
            ssh.close()
            
            # 更新日志
            task_log.output = output
            task_log.error = error
            
            # 检查是否有错误
            if error:
                self.status = 'failed'
                task_log.status = 'failed'
                result = False, f"命令执行失败: {error}"
            else:
                self.status = 'completed'
                task_log.status = 'completed'
                result = True, "命令执行成功"
                
            # 记录完成时间
            task_log.completed_at = timezone.now()
            task_log.save()
            self.save()
            
            return result
            
        except Exception as e:
            error_msg = f"执行任务时发生错误: {str(e)}"
            logger.error(error_msg)
            
            # 更新状态
            self.status = 'failed'
            self.save()
            
            # 更新日志
            task_log.status = 'failed'
            task_log.error = error_msg
            task_log.completed_at = timezone.now()
            task_log.save()
            
            return False, error_msg


class TaskLog(models.Model):
    """任务执行日志模型"""
    LOG_STATUS_CHOICES = [
        ('running', '运行中'),
        ('completed', '已完成'),
        ('failed', '已失败'),
    ]
    
    #id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    task = models.ForeignKey(Task, on_delete=models.CASCADE, related_name="logs", verbose_name="关联任务")
    status = models.CharField(max_length=20, choices=LOG_STATUS_CHOICES, default='running', verbose_name="日志状态")
    output = models.TextField(blank=True, null=True, verbose_name="命令输出")
    error = models.TextField(blank=True, null=True, verbose_name="错误信息")
    started_at = models.DateTimeField(auto_now_add=True, verbose_name="开始时间")
    completed_at = models.DateTimeField(blank=True, null=True, verbose_name="完成时间")
    started_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="initiated_task_logs", verbose_name="执行人")
    
    class Meta:
        verbose_name = "任务日志"
        verbose_name_plural = "任务日志"
        ordering = ['-started_at']
    
    def __str__(self):
        return f"{self.task.name} - {self.status} - {self.started_at.strftime('%Y-%m-%d %H:%M:%S')}"
    
    @property
    def duration(self):
        """计算任务执行时长"""
        if self.completed_at and self.started_at:
            delta = self.completed_at - self.started_at
            return str(delta)
        return "未知"


class SystemLog(models.Model):
    """系统操作日志模型"""
    LOG_LEVEL_CHOICES = [
        ('info', '信息'),
        ('warning', '警告'),
        ('error', '错误'),
        ('critical', '严重'),
    ]
    
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    level = models.CharField(max_length=10, choices=LOG_LEVEL_CHOICES, default='info', verbose_name="日志级别")
    message = models.TextField(verbose_name="日志消息")
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="system_logs", verbose_name="操作用户")
    ip_address = models.GenericIPAddressField(blank=True, null=True, verbose_name="IP地址")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    
    class Meta:
        verbose_name = "系统日志"
        verbose_name_plural = "系统日志"
        ordering = ['-created_at']
    
    def __str__(self):
        return f"{self.get_level_display()} - {self.message[:50]} - {self.created_at.strftime('%Y-%m-%d %H:%M:%S')}"



# 新增数据库管理模块模型
class DatabaseConnection(models.Model):
    """数据库连接配置"""
    DB_TYPES = (
        ('mysql', 'MySQL'),
        ('sqlserver', 'SQL Server'),
        ('oracle', 'Oracle'),
    )
    
    name = models.CharField(max_length=100, verbose_name="连接名称")
    db_type = models.CharField(max_length=20, choices=DB_TYPES, verbose_name="数据库类型")
    host = models.GenericIPAddressField(verbose_name="IP地址")
    port = models.IntegerField(verbose_name="端口")
    instance_name = models.CharField(max_length=100, blank=True, null=True, verbose_name="实例名")
    username = models.CharField(max_length=100, verbose_name="用户名")
    password = models.CharField(max_length=255, verbose_name="密码")
    database_name = models.CharField(max_length=100, verbose_name="数据库名")
    description = models.TextField(blank=True, null=True, verbose_name="描述")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="创建时间")
    updated_at = models.DateTimeField(auto_now=True, verbose_name="更新时间")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="db_connections", verbose_name="创建人")
    is_active = models.BooleanField(default=True, verbose_name="是否激活")
    
    def __str__(self):
        return f"{self.name} ({self.get_db_type_display()})"
    
    class Meta:
        verbose_name = "数据库连接配置"
        verbose_name_plural = "数据库连接配置"
        ordering = ['name']

class QueryHistory(models.Model):
    """SQL查询历史记录"""
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    db_connection = models.ForeignKey(DatabaseConnection, on_delete=models.CASCADE, related_name="query_histories", verbose_name="数据库连接")
    sql = models.TextField(verbose_name="SQL语句")
    execution_time = models.FloatField(null=True, blank=True, verbose_name="执行时间(秒)")
    row_count = models.IntegerField(null=True, blank=True, verbose_name="返回行数")
    created_at = models.DateTimeField(auto_now_add=True, verbose_name="执行时间")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="query_histories", verbose_name="执行人")
    is_success = models.BooleanField(default=True, verbose_name="是否成功")
    error_message = models.TextField(blank=True, null=True, verbose_name="错误信息")
    
    def __str__(self):
        return f"Query on {self.db_connection.name} at {self.created_at.strftime('%Y-%m-%d %H:%M')}"
    
    class Meta:
        verbose_name = "查询历史"
        verbose_name_plural = "查询历史"
        ordering = ['-created_at']
